package org.anyline.simple.postgres;

import org.anyline.data.param.ConfigStore;
import org.anyline.data.param.TableBuilder;
import org.anyline.data.param.init.DefaultConfigStore;
import org.anyline.data.prepare.RunPrepare;
import org.anyline.entity.DataRow;
import org.anyline.metadata.Table;
import org.anyline.proxy.ServiceProxy;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest(classes = PostgresApplication.class)
public class UpdateTest {
    /* **********************************************************************************
    *
    *                           只有 多表关联时才需要以下示例
    *                           单表 update(table, data)即可
    *
    * ***********************************************************************************/
    /**
     * 先创建测试表
     * @throws Exception Exception
     */
    @Test
    public void init() throws Exception {
        init("FI_USER");
        init("HR_USER");
        init("MM_USER");
    }
    public void init(String name) throws Exception {
        Table table = ServiceProxy.metadata().table(name, false);
        if(null != table){
            ServiceProxy.ddl().drop(table);
        }
        table = new Table(name);
        table.addColumn("ID", "BIGINT").setPrimary(true).setAutoIncrement(true);
        table.addColumn("CODE", "VARCHAR(32)");
        table.addColumn("NAME", "VARCHAR(10)");
        table.addColumn("TYPE_CODE", "VARCHAR(10)");
        table.addColumn("LVL", "INT");
        table.addColumn("REMARK", "VARCHAR(100)");
        ServiceProxy.ddl().create(table);
    }


    @Test
    public void builder1(){
        RunPrepare prepare = TableBuilder.init("FI_USER AS FI")
                .left("HR_USER AS HR", "HR.ID = FI.ID")
                .build();
        DataRow data = new DataRow();
        data.put("CODE", 1);
        data.putVar("NAME", "HR.NAME");
        //过滤条件也可以通过TableBuilder或RunPrepare设置
        ConfigStore configs = new DefaultConfigStore();
        configs.and("FI.ID > 10");
        ServiceProxy.service().update(prepare, data, configs, "HR.TYPE_CODE:100");
        /*
            UPDATE FI_USER FI
            SET
            CODE = ?, NAME = HR.NAME
            FROM HR_USER HR
            WHERE (HR.TYPE_CODE = ?
                 AND (FI.ID > 10 AND HR.ID = FI.ID))

            param0=1(java.lang.Integer)
            param1=100(java.lang.String)
        */
    }
    @Test
    public void builder_inner1(){
        //子查询
        RunPrepare inner_hr = TableBuilder.init("HR_USER(ID AS HR_ID, CODE AS HR_CODE, NAME) AS HR").build();

        RunPrepare master = TableBuilder.init("FI_USER AS M")   //()内指定的是最外层的查询列名，放在主表名容易误解，可以addColumns()单独指定
                .left("HRS", inner_hr, "HRS.HR_ID = M.ID", "HRS.HR_CODE = M.CODE")    //主表的表名列名要用原名 这里的子查的表名列名注意用 别名 HRS是当前子查询的别名
                .left("MM_USER AS MM", "MM.ID = HRS.HR_ID")
            .build();
        DataRow data = new DataRow();
        data.put("CODE", 1);
        data.putVar("NAME", "HRS.NAME");
        ConfigStore configs = new DefaultConfigStore();
        configs.and("M.ID > 10");
        ServiceProxy.service().update(master, data, configs, "M.TYPE_CODE:100");
        /* 注意区分内外层 别名
            UPDATE FI_USER M
            SET
            CODE = ?, NAME = HRS.NAME
            FROM (
                SELECT
                    ID HR_ID, CODE HR_CODE, NAME
                FROM HR_USER HR
            ) HRS
            LEFT JOIN MM_USER MM ON MM.ID = HRS.HR_ID
            WHERE (M.TYPE_CODE = ?
                 AND (M.ID > 10
                         AND (HRS.HR_ID = M.ID AND HRS.HR_CODE = M.CODE)))

            param0=1(java.lang.Integer)
            param1=100(java.lang.String)
        */
    }


}
